Credit

Indexing

• Utilize space to improve time complexity in search records
• Commonly used B-Tree
• Balance to Read-Write as writing data is slower now

Materialized Views

• To resolve the expansive queries on the fly, Save the data to materialized views to lower computational load.
• Tableau
• Balance between refresh frequency and performance benefits.

Vertical Scaling

Boot the DB server by adding more CPU, RAM, or storage.

Sharding

• Load resources that page will need before they are needed
• horizontal scaling
• Designing a sharding database is complex
• super complex on querying logic as data can be distributed in any database shards

Replication

Create replicas of primary database on servers for scaling the reads

• Asynchronous
  • Temporary inconsistent in reads as it takes time to update on replicas
  • faster and take less resources on the primary machines
• Synchronous
  • Consistent
  • update requests takes longer to finish as all replicas need to ack on the update.

Caching

• Less hit on the DB to lower stress on DB side.
• Cache in memory: Redis
• Cache in application

Denormalization

Reduce complex join to improve query performance. Again! trade space for time complexity!